﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class MovementDetailDA : IMovementDetailDA
    {
        private string CREATED_BY = "@CREATED_BY";
        private string FROM_LOC = "@FROM_LOC";
        private string FROM_LPN_ID = "@FROM_LPN_ID";
        private string FROM_QTY = "@FROM_QTY";
        private string LINE_ID = "@LINE_ID";
        private string LOT = "@LOT";
        private string MOVEMENT_ID = "@MOVEMENT_ID";
        private string OWNER_ID = "@OWNER_ID";
        private string PACK_ID = "@PACK_ID";
        private string QTY = "@QTY";
        private string REASON_CODE = "@REASON_CODE";
        private string REMARK = "@REMARK";
        private string SKU_ID = "@SKU_ID";
        private string SQL_CHECK_MOVEMENT_DETAIL_ID_UNIQUE = " SELECT COUNT(1) FROM @_@MOVEMENT_DETAIL WHERE MOVEMENT_ID = @MOVEMENT_ID AND LINE_ID = @LINE_ID ";
        private string SQL_DELETE_MOVEMENT_DETAIL = " DELETE FROM @_@MOVEMENT_DETAIL WHERE MOVEMENT_ID = @MOVEMENT_ID";
        private string SQL_INSERT_MOVEMENT_DETAIL = " INSERT INTO @_@MOVEMENT_DETAIL ( WH_ID, MOVEMENT_ID, LINE_ID, STATUS, OWNER_ID, SKU_ID, LOT, FROM_LOC, FROM_LPN_ID, TO_LOC, TO_LPN_ID, REASON_CODE, UOM_ID, PACK_ID, FROM_QTY, QTY, REMARK, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @MOVEMENT_ID, @LINE_ID, @STATUS, @OWNER_ID, @SKU_ID, @LOT, @FROM_LOC, @FROM_LPN_ID, @TO_LOC, @TO_LPN_ID, @REASON_CODE, @UOM_ID, @PACK_ID, @FROM_QTY, @QTY, @REMARK, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_MOVEMENT_DETAIL = " SELECT  ROWID, WH_ID, MOVEMENT_ID, LINE_ID, STATUS, OWNER_ID, SKU_ID, LOT, FROM_LOC, FROM_LPN_ID, TO_LOC, TO_LPN_ID, REASON_CODE, UOM_ID, PACK_ID, FROM_QTY, QTY, REMARK, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE  FROM  @_@MOVEMENT_DETAIL ";
        private string SQL_SELECT_ALL_MOVEMENT_DETAIL_INV_LOT_LOC_LPN = "  SELECT A.ROWID,A.WH_ID,A.MOVEMENT_ID,A.LINE_ID, A.OWNER_ID,A.SKU_ID,A.LOT,A.FROM_LPN_ID,A.FROM_QTY,A.FROM_LOC,A.REASON_CODE,A.REMARK, A.CREATED_BY, A.CREATED_DATE, A.UPDATED_BY, A.UPDATED_DATE,B.QTY-B.ALLOCATED_QTY-B.PICKED_QTY-B.EXPECTED_QTY- B.PICKINPROCESS_QTY AS AVAILABLEQTY,B.ALLOCATED_QTY AS ALLOCATEDQTY,A.TO_LPN_ID,A.TO_LOC,A.QTY, A.PACK_ID,A.UOM_ID,A.STATUS,T.[TEXT] AS STATUS_D ,C.DESCR AS SKU_DESCR,   D.VENDOR_ID,D.EXTERNAL_LOT,D.PRODUCE_DATE,D.EXPIRY_DATE,D.RECEIPT_DATE, D.LOT_ATTR01 ,D.LOT_ATTR02 ,D.LOT_ATTR03 ,D.LOT_ATTR04 ,D.LOT_ATTR05,D.LOT_ATTR06 ,D.LOT_ATTR07 ,D.LOT_ATTR08,D.LOT_ATTR09,D.LOT_ATTR10,B.STATUS AS HOLD_STATUS    FROM @_@MOVEMENT_DETAIL A  LEFT JOIN  @_@INV_LOT_LOC_LPN   B ON A.FROM_LOC = B.LOC AND A.LOT=B.LOT AND A.FROM_LPN_ID=LPN_ID  LEFT JOIN @_@SKU C ON A.SKU_ID = C.SKU_ID AND A.OWNER_ID = C.OWNER_ID  LEFT JOIN @_@LOT_ATTRIBUTE D ON A.LOT = D.LOT   LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = A.STATUS AND SCD.SYS_CODE_TYPE = 'MovementStatus'   LEFT JOIN sysadm.TRANSLATION T ON 'MovementStatus.'+A.STATUS = T.ITEM_ID   AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = '@LANGUAGE'";
        private string SQL_UPDATE_MOVEMENT_DETAIL = " UPDATE @_@MOVEMENT_DETAIL SET WH_ID = @WH_ID, STATUS = @STATUS, OWNER_ID = @OWNER_ID, SKU_ID = @SKU_ID, LOT = @LOT, FROM_LOC = @FROM_LOC, FROM_LPN_ID = @FROM_LPN_ID, TO_LOC = @TO_LOC, TO_LPN_ID = @TO_LPN_ID, REASON_CODE = @REASON_CODE, UOM_ID = @UOM_ID, PACK_ID = @PACK_ID, FROM_QTY = @FROM_QTY, QTY = @QTY, REMARK = @REMARK, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE MOVEMENT_ID = @MOVEMENT_ID AND LINE_ID = @LINE_ID  ";
        private string SQL_UPDATE_STATUS = "UPDATE @_@MOVEMENT_DETAIL SET STATUS =@STATUS WHERE MOVEMENT_ID=@MOVEMENT_ID";
        private string STATUS = "@STATUS";
        private string TO_LOC = "@TO_LOC";
        private string TO_LPN_ID = "@TO_LPN_ID";
        private string UOM_ID = "@UOM_ID";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WH_ID = "@WH_ID";

        public bool CheckMovementDetailIDUnique(string movementDetailID, string lineID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@MOVEMENT_ID", movementDetailID), new SqlParameter("@LINE_ID", lineID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_MOVEMENT_DETAIL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckMovementDetailIDUnique(DataBase dataBase, DbTransaction tran, string movementDetailID, string lineID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@MOVEMENT_ID", movementDetailID), new SqlParameter("@LINE_ID", lineID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_MOVEMENT_DETAIL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteMovementDetail(string MovementID, List<string> lineID, string whLoginID)
        {
            int result = 0;
            string sql = this.SQL_DELETE_MOVEMENT_DETAIL.Replace("@_@", whLoginID) + " AND LINE_ID = @LINE_ID ";
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int j = 0; j < lineID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@MOVEMENT_ID", MovementID), new SqlParameter("@LINE_ID", lineID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteMovementDetail(DataBase dataBase, DbTransaction tran, List<string> MovementID, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = this.SQL_DELETE_MOVEMENT_DETAIL.Replace("@_@", whLoginID);
                for (int j = 0; j < MovementID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@MOVEMENT_ID", MovementID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, sql.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteMovementDetail(DataBase dataBase, DbTransaction tran, string MovementID, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@MOVEMENT_ID", MovementID) };
                result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_MOVEMENT_DETAIL.Replace("@_@", whLoginID), paras);
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteMovementDetail(DataBase dataBase, DbTransaction tran, string MovementID, List<string> lineID, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = this.SQL_DELETE_MOVEMENT_DETAIL.Replace("@_@", whLoginID) + " AND LINE_ID = @LINE_ID ";
                for (int j = 0; j < lineID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@MOVEMENT_ID", MovementID), new SqlParameter("@LINE_ID", lineID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, sql.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllMovementDetail(string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_MOVEMENT_DETAIL_INV_LOT_LOC_LPN.Replace("@_@", whLoginID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllMovementDetail(DataBase dataBase, DbTransaction tran, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_MOVEMENT_DETAIL_INV_LOT_LOC_LPN.Replace("@_@", whLoginID);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public List<MovementDetailInfo> GetMovementDetailByID(string movementID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_MOVEMENT_DETAIL_INV_LOT_LOC_LPN.Replace("@_@", whLoginID) + " WHERE  A.MOVEMENT_ID = @MOVEMENT_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@MOVEMENT_ID", movementID) };
            List<MovementDetailInfo> movementDetailList = new List<MovementDetailInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql.Replace("@LANGUAGE", pubParasInfo.UserLanguage), paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    MovementDetailInfo movementDetailInfo = new MovementDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["MOVEMENT_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["STATUS"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["LOT"].ToString(), reader["FROM_LOC"].ToString(), reader["FROM_LPN_ID"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LPN_ID"].ToString(), reader["REASON_CODE"].ToString(), reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), (reader["FROM_QTY"].ToString() != "") ? decimal.Parse(reader["FROM_QTY"].ToString()) : 0M, (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["REMARK"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["AVAILABLEQTY"].ToString() != "") ? decimal.Parse(reader["AVAILABLEQTY"].ToString()) : 0M, reader["SKU_DESCR"].ToString(), (reader["ALLOCATEDQTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATEDQTY"].ToString()) : 0M, reader["VENDOR_ID"].ToString(), reader["EXTERNAL_LOT"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), reader["HOLD_STATUS"].ToString(), reader["STATUS_D"].ToString());
                    movementDetailList.Add(movementDetailInfo);
                }
            }
            return movementDetailList;
        }

        public MovementDetailInfo GetMovementDetailByID(string movementID, string LineID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_MOVEMENT_DETAIL_INV_LOT_LOC_LPN.Replace("@_@", whLoginID) + " WHERE A.MOVEMENT_ID = @MOVEMENT_ID AND  A.LINE_ID = @LINE_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@MOVEMENT_ID", movementID), new SqlParameter("@LINE_ID", LineID) };
            MovementDetailInfo movementDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql.Replace("@LANGUAGE", pubParasInfo.UserLanguage), paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    movementDetailInfo = new MovementDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["MOVEMENT_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["STATUS"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["LOT"].ToString(), reader["FROM_LOC"].ToString(), reader["FROM_LPN_ID"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LPN_ID"].ToString(), reader["REASON_CODE"].ToString(), reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), (reader["FROM_QTY"].ToString() != "") ? decimal.Parse(reader["FROM_QTY"].ToString()) : 0M, (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["REMARK"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["AVAILABLEQTY"].ToString() != "") ? decimal.Parse(reader["AVAILABLEQTY"].ToString()) : 0M, reader["SKU_DESCR"].ToString(), (reader["ALLOCATEDQTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATEDQTY"].ToString()) : 0M, reader["VENDOR_ID"].ToString(), reader["EXTERNAL_LOT"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : new DateTime(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), reader["HOLD_STATUS"].ToString(), reader["STATUS_D"].ToString());
                }
            }
            return movementDetailInfo;
        }

        public MovementDetailInfo GetMovementDetailByID(DataBase dataBase, DbTransaction tran, string movementID, string lineID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_MOVEMENT_DETAIL_INV_LOT_LOC_LPN.Replace("@_@", whLoginID) + " WHERE  A.MOVEMENT_ID = @MOVEMENT_ID AND  A.LINE_ID = @LINE_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@MOVEMENT_ID", movementID), new SqlParameter("@LINE_ID", lineID) };
            MovementDetailInfo movementDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql.Replace("@LANGUAGE", pubParasInfo.UserLanguage), paras);
            if (reader.Read())
            {
                DateTime CS00003;
                movementDetailInfo = new MovementDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["MOVEMENT_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["STATUS"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["LOT"].ToString(), reader["FROM_LOC"].ToString(), reader["FROM_LPN_ID"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LPN_ID"].ToString(), reader["REASON_CODE"].ToString(), reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), (reader["FROM_QTY"].ToString() != "") ? decimal.Parse(reader["FROM_QTY"].ToString()) : 0M, (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, reader["REMARK"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["AVAILABLEQTY"].ToString() != "") ? decimal.Parse(reader["AVAILABLEQTY"].ToString()) : 0M, reader["SKU_DESCR"].ToString(), (reader["ALLOCATEDQTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATEDQTY"].ToString()) : 0M, reader["VENDOR_ID"].ToString(), reader["EXTERNAL_LOT"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : new DateTime(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), reader["HOLD_STATUS"].ToString(), reader["STATUS_D"].ToString());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return movementDetailInfo;
        }

        public DataSet GetMovementDetailByQueryList(List<string> sqlWhere, MovementDetailQueryEntity movementdetailQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_MOVEMENT_DETAIL_INV_LOT_LOC_LPN.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!movementdetailQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, movementdetailQuery.CurrentPage, movementdetailQuery.PageSize, movementdetailQuery.SortField, movementdetailQuery.SortDirection);
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetMovementDetailByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, MovementDetailQueryEntity movementdetailQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_MOVEMENT_DETAIL_INV_LOT_LOC_LPN.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            temp = PagingHelper.GetPagingSQL(temp, movementdetailQuery.CurrentPage, movementdetailQuery.PageSize, movementdetailQuery.SortField, movementdetailQuery.SortDirection);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public int InsertMovementDetail(MovementDetailInfo movementDetailInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                movementDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_MovementDetail_Parameters(movementDetailInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_MOVEMENT_DETAIL.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertMovementDetail(DataBase dataBase, DbTransaction tran, MovementDetailInfo movementDetailInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                movementDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_MovementDetail_Parameters(movementDetailInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_MOVEMENT_DETAIL.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_MovementDetail_Parameters(MovementDetailInfo movementDetailInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.MOVEMENT_ID, SqlDbType.VarChar, 20), new SqlParameter(this.LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.LOT, SqlDbType.VarChar, 20), new SqlParameter(this.FROM_LOC, SqlDbType.VarChar, 20), new SqlParameter(this.FROM_LPN_ID, SqlDbType.VarChar, 20), new SqlParameter(this.TO_LOC, SqlDbType.VarChar, 20), new SqlParameter(this.TO_LPN_ID, SqlDbType.VarChar, 20), new SqlParameter(this.REASON_CODE, SqlDbType.VarChar, 20), new SqlParameter(this.UOM_ID, SqlDbType.VarChar, 20), new SqlParameter(this.PACK_ID, SqlDbType.VarChar, 50), new SqlParameter(this.FROM_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.QTY, SqlDbType.Decimal, 13), 
                new SqlParameter(this.REMARK, SqlDbType.VarChar, 200), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20)
             };
            if (!string.IsNullOrEmpty(movementDetailInfo.WhID))
            {
                paramArray[0].Value = movementDetailInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = movementDetailInfo.MovementID;
            paramArray[2].Value = movementDetailInfo.LineID;
            paramArray[3].Value = movementDetailInfo.Status;
            paramArray[4].Value = movementDetailInfo.OwnerID;
            paramArray[5].Value = movementDetailInfo.SkuID;
            paramArray[6].Value = movementDetailInfo.Lot;
            paramArray[7].Value = movementDetailInfo.FromLoc;
            paramArray[8].Value = movementDetailInfo.FromLpnID;
            paramArray[9].Value = movementDetailInfo.ToLoc;
            paramArray[10].Value = movementDetailInfo.ToLpnID;
            paramArray[11].Value = movementDetailInfo.ReasonCode;
            paramArray[12].Value = movementDetailInfo.UomID;
            paramArray[13].Value = movementDetailInfo.PackID;
            paramArray[14].Value = movementDetailInfo.FromQty;
            paramArray[15].Value = movementDetailInfo.Qty;
            if (!string.IsNullOrEmpty(movementDetailInfo.Remark))
            {
                paramArray[0x10].Value = movementDetailInfo.Remark;
            }
            else
            {
                paramArray[0x10].Value = DBNull.Value;
            }
            paramArray[0x11].Value = movementDetailInfo.CreatedBy;
            paramArray[0x12].Value = movementDetailInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdateMovementDetail(MovementDetailInfo movementDetailInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                movementDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_MovementDetail_Parameters(movementDetailInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_MOVEMENT_DETAIL.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateMovementDetail(DataBase dataBase, DbTransaction tran, MovementDetailInfo movementDetailInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                movementDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_MovementDetail_Parameters(movementDetailInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_MOVEMENT_DETAIL.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateMovementDetailStatus(DataBase dataBase, DbTransaction tran, string MovementID, string Status, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@MOVEMENT_ID", MovementID), new SqlParameter("@STATUS", Status) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_STATUS.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

